﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data.SqlClient;
using System.Data;
using ActivePalInterop.Data;
using ActivePalInterop.Events;
using System.Threading;
using System.Collections.ObjectModel;

namespace ActivePalInterop
{
    /// <summary>
    /// Contains methods for DB interactions
    /// </summary>
    public class DbFacade
    {
        #region Singleton instance

        private static object s_InternalSyncObject;
        private static DbFacade instance;

        /// <summary>
        /// Blocking object
        /// </summary>
        private static object InternalSyncObject
        {
            get
            {
                if (s_InternalSyncObject == null)
                {
                    object obj2 = new object();
                    Interlocked.CompareExchange(ref s_InternalSyncObject, obj2, null);
                }
                return s_InternalSyncObject;
            }
        }

        /// <summary>
        /// Singleton Instance of object
        /// </summary>
        public static DbFacade GetInstance()
        {
            if (instance == null)
            {
                lock (InternalSyncObject)
                {
                    if (instance == null)
                    {
                        instance = new DbFacade();
                    }
                }
            }
            return instance;
        }

        #endregion

        #region Event DebugMessage

        /// <summary>
        /// Generates a debug message that can be added to a GUI output
        /// </summary>
        public event StaticMessageEventHandler DebugMessage;

        /// <summary>
        /// Invokes DebugMessage event
        /// </summary>        
        public void InvokeDebugMessage(string message)
        {
            if (DebugMessage != null)
            {
                DebugMessage(new MessageEventArgs(message));
            }
        }

        #endregion

        /// <summary>
        /// Connection string
        /// </summary>
        public string ConnString { get; set; }

        /// <summary>
        /// Open and close connection for a test.
        /// </summary>
        public void TestConnection()
        {
            try
            {
                SqlConnection connection = new SqlConnection(ConnString);
                connection.Open();
                connection.Close();
                InvokeDebugMessage("Connection to database established.");
            }
            catch (Exception ex)
            {
                InvokeDebugMessage("Connection to database failed: " + ex.Message);
            }
        }

        /// <summary>
        /// Open and close connection for a test.
        /// </summary>
        public string TestConnection(string tempConString)
        {
            try
            {
                SqlConnection connection = new SqlConnection(tempConString);
                connection.Open();
                connection.Close();
                return String.Empty;
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        /// <summary>
        /// Uses SqlBulkCopy class to write a datatable to a database
        /// </summary>
        /// <param name="destinationTableName">table name to write to</param>
        /// <param name="localDataTable">cached datatable</param>
        public void WriteToDatabase(string destinationTableName, DataTable localDataTable)
        {
            try
            {
                // connect to SQL            
                using (SqlConnection connection = new SqlConnection(ConnString))
                {
                    connection.Open();

                    // make sure to enable triggers
                    // more on triggers in next post
                    SqlBulkCopy bulkCopy = new SqlBulkCopy
                                                (
                                                    connection,
                                                    SqlBulkCopyOptions.TableLock |
                                                    SqlBulkCopyOptions.FireTriggers |
                                                    SqlBulkCopyOptions.UseInternalTransaction,
                                                    null
                                                );

                    // set the destination table name
                    bulkCopy.DestinationTableName = destinationTableName;

                    // write the data in the "dataTable"
                    bulkCopy.WriteToServer(localDataTable);
                    connection.Close();
                }
            }
            catch (Exception ex)
            {
                InvokeDebugMessage("Database failed: " + ex.Message);
            }
        }

        /// <summary>
        /// Calls a SP to create a new session in a db
        /// </summary>
        /// <param name="session">Current session</param>
        public void CreateSession(Session session)
        {
            try
            {
                string query = "sp_create_session";

                // connect to SQL
                using (SqlConnection connection = new SqlConnection(ConnString))
                {
                    connection.Open();
                    using (SqlTransaction trans = connection.BeginTransaction())
                    {
                        using (SqlCommand cmd = new SqlCommand(query, connection))
                        {
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.Transaction = trans;

                            SqlParameter p1 = new SqlParameter("SESSION_ID", typeof(Guid));
                            SqlParameter p2 = new SqlParameter("DATE_FROM", typeof(DateTime));
                            SqlParameter p3 = new SqlParameter("DATE_TO", typeof(DateTime));
                            SqlParameter p4 = new SqlParameter("DATA_FILE_PATH", typeof(string));
                            SqlParameter p5 = new SqlParameter("MONITOR_SERIAL_NUMBER", typeof(string));
                            SqlParameter p6 = new SqlParameter("SESSION_NAME", typeof(string));
                            SqlParameter p7 = new SqlParameter("SESSION_COMMENTS", typeof(string));
                            SqlParameter p8 = new SqlParameter("PERSON_ID", typeof(Guid));

                            p1.Value = session.SessionId;
                            p2.Value = session.DateFrom;
                            p3.Value = session.DateTo;
                            p4.Value = session.DataFilePath;
                            p5.Value = session.MonitorSerialNumber;
                            p6.Value = session.SessionName;
                            p7.Value = session.SessionComments;
                            p8.Value = session.PersonId;

                            cmd.Parameters.Add(p1);
                            cmd.Parameters.Add(p2);
                            cmd.Parameters.Add(p3);
                            cmd.Parameters.Add(p4);
                            cmd.Parameters.Add(p5);
                            cmd.Parameters.Add(p6);
                            cmd.Parameters.Add(p7);
                            cmd.Parameters.Add(p8);

                            cmd.ExecuteNonQuery();
                            trans.Commit();

                            connection.Close();
                            InvokeDebugMessage("Session created.");
                        }
                    }

                }
            }
            catch (Exception ex)
            {
                InvokeDebugMessage("Database failed: " + ex.Message);
            }
        }

        /// <summary>
        /// Calls a SP to create a new person in a db
        /// </summary>
        /// <param name="person">Person object to be created</param>
        /// <returns>true if created, otherwise false</returns>
        public bool CreatePerson(Person person)
        {
            try
            {
                string query = "sp_create_person";

                // connect to SQL
                using (SqlConnection connection = new SqlConnection(ConnString))
                {
                    connection.Open();
                    using (SqlTransaction trans = connection.BeginTransaction())
                    {
                        using (SqlCommand cmd = new SqlCommand(query, connection))
                        {
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.Transaction = trans;

                            SqlParameter p1 = new SqlParameter("PERSON_ID", typeof(Guid));
                            SqlParameter p2 = new SqlParameter("FIRST_NAME", typeof(string));
                            SqlParameter p3 = new SqlParameter("LAST_NAME", typeof(string));
                            SqlParameter p4 = new SqlParameter("AGE", typeof(int));
                            SqlParameter p5 = new SqlParameter("GENDER", typeof(char));
                            SqlParameter p6 = new SqlParameter("BMI", typeof(double));
                            SqlParameter p7 = new SqlParameter("HEIGHT", typeof(double));
                            SqlParameter p8 = new SqlParameter("WEIGHT", typeof(double));
                            SqlParameter p9 = new SqlParameter("DESEASE", typeof(string));

                            p1.Value = person.PersonId;
                            p2.Value = person.FirstName;
                            p3.Value = person.LastName;
                            p4.Value = person.Age;
                            p5.Value = GenderConverter.ToChar(person.Gender);
                            p6.Value = person.BMI;
                            p7.Value = person.Height;
                            p8.Value = person.Weight;
                            p9.Value = person.Desease;

                            cmd.Parameters.Add(p1);
                            cmd.Parameters.Add(p2);
                            cmd.Parameters.Add(p3);
                            cmd.Parameters.Add(p4);
                            cmd.Parameters.Add(p5);
                            cmd.Parameters.Add(p6);
                            cmd.Parameters.Add(p7);
                            cmd.Parameters.Add(p8);
                            cmd.Parameters.Add(p9);

                            cmd.ExecuteNonQuery();
                            trans.Commit();

                            connection.Close();                           
                            return true;
                        }
                    }

                }
            }
            catch (Exception ex)
            {
                InvokeDebugMessage("Database failed: " + ex.Message);
                return false;
            }
        }

        /// <summary>
        /// Calls SP, selects all people that are in the db
        /// </summary>
        /// <returns>Observable collection of people</returns>
        public ObservableCollection<Person> GetPeople()
        {
            try
            {
                string query = "sp_get_all_people";
                ObservableCollection<Person> people = null;

                // connect to SQL
                using (SqlConnection connection = new SqlConnection(ConnString))
                {
                    connection.Open();
                    using (SqlCommand cmd = new SqlCommand(query, connection))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;

                        SqlDataReader reader = cmd.ExecuteReader();
                        people = new ObservableCollection<Person>();

                        while (reader.Read())
                        {
                            Person person = new Person();
                            
                            person.PersonId = (Guid)reader["person_id"];
                            person.FirstName = (string)reader["first_name"];
                            person.LastName = (string)reader["last_name"];
                            person.Age = (double)reader["age"];
                            person.Gender = GenderConverter.ToGender(Convert.ToChar(reader["gender"]));
                            person.BMI = (double)reader["bmi"];
                            person.Height = (double)reader["height"];
                            person.Weight = (double)reader["weight"];
                            person.Desease = (string)reader["desease"];
                        
                            people.Add(person);
                        }
                        reader.Close();
                        connection.Close();                        
                    }
                }
                return people;
            }
            catch (Exception ex)
            {
                InvokeDebugMessage("Database failed:" + ex.Message);
                return null;
            }
        }

        internal bool UpdatePerson(Person person)
        {
            try
            {
                string query = "sp_update_person";

                // connect to SQL
                using (SqlConnection connection = new SqlConnection(ConnString))
                {
                    connection.Open();
                    using (SqlTransaction trans = connection.BeginTransaction())
                    {
                        using (SqlCommand cmd = new SqlCommand(query, connection))
                        {
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.Transaction = trans;

                            SqlParameter p1 = new SqlParameter("PERSON_ID", typeof(Guid));
                            SqlParameter p2 = new SqlParameter("FIRST_NAME", typeof(string));
                            SqlParameter p3 = new SqlParameter("LAST_NAME", typeof(string));
                            SqlParameter p4 = new SqlParameter("AGE", typeof(int));
                            SqlParameter p5 = new SqlParameter("GENDER", typeof(char));
                            SqlParameter p6 = new SqlParameter("BMI", typeof(double));
                            SqlParameter p7 = new SqlParameter("HEIGHT", typeof(double));
                            SqlParameter p8 = new SqlParameter("WEIGHT", typeof(double));
                            SqlParameter p9 = new SqlParameter("DESEASE", typeof(string));

                            p1.Value = person.PersonId;
                            p2.Value = person.FirstName;
                            p3.Value = person.LastName;
                            p4.Value = person.Age;
                            p5.Value = GenderConverter.ToChar(person.Gender);
                            p6.Value = person.BMI;
                            p7.Value = person.Height;
                            p8.Value = person.Weight;
                            p9.Value = person.Desease;

                            cmd.Parameters.Add(p1);
                            cmd.Parameters.Add(p2);
                            cmd.Parameters.Add(p3);
                            cmd.Parameters.Add(p4);
                            cmd.Parameters.Add(p5);
                            cmd.Parameters.Add(p6);
                            cmd.Parameters.Add(p7);
                            cmd.Parameters.Add(p8);
                            cmd.Parameters.Add(p9);

                            cmd.ExecuteNonQuery();
                            trans.Commit();

                            connection.Close();
                            return true;
                        }
                    }

                }
            }
            catch (Exception ex)
            {
                InvokeDebugMessage("Database failed: " + ex.Message);
                return false;
            }
        }

        internal bool DeletePerson(Person person)
        {
            try
            {
                string query = "sp_delete_person";

                // connect to SQL
                using (SqlConnection connection = new SqlConnection(ConnString))
                {
                    connection.Open();
                    using (SqlTransaction trans = connection.BeginTransaction())
                    {
                        using (SqlCommand cmd = new SqlCommand(query, connection))
                        {
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.Transaction = trans;

                            SqlParameter p1 = new SqlParameter("PERSON_ID", typeof(Guid));
                           
                            p1.Value = person.PersonId;
                           
                            cmd.Parameters.Add(p1);
                           
                            cmd.ExecuteNonQuery();
                            trans.Commit();

                            connection.Close();
                            return true;
                        }
                    }

                }
            }
            catch (Exception ex)
            {
                InvokeDebugMessage("Database failed: " + ex.Message);
                return false;
            }
        }
    }
}
